﻿// Copyright: 2012 
// Author: Minh Vu - YoungJ
// File name: Database.cs
// Solution: iTunesApp
// Project: iTunesApp
// Time: 12:14 PM 10/05/2012

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlServerCe;
using System.Diagnostics;
using System.IO;
using System.Windows.Forms;
using System.Linq;

namespace iTunesApp
{
    public class Database
    {
        private SqlCeConnection _connection = new SqlCeConnection();
        private bool connectSuccess;
        public Database()
        {
            try
            {
                _connection.ConnectionString = "data source=iTunesApp.sdf";
                _connection.Open();
                Debug.WriteLine("connection ok");
                connectSuccess = true;

            }
            catch (Exception exception)
            {
                Debug.WriteLine("connection fauilre: " + exception.Message);
                connectSuccess = false;
            }
            finally
            {
                _connection.Close();
            }

        }


        public IEnumerable<Request> GetAll()
        {
            if (!connectSuccess) return null;
            const string query = @"SELECT Id, Artist, AlbumSingle, BackupAccount, FBAlbum, Url FROM  Request";

            try
            {
                var cmd = new SqlCeCommand(query, _connection);
                if (_connection.State == ConnectionState.Closed)
                    _connection.Open();
                var rdr = cmd.ExecuteReader();
                var list = new List<Request>();
                while (rdr.Read())
                {
                    list.Add(new Request
                                 {
                                     Id = rdr.GetInt32(0),
                                     Artist = rdr.GetString(1),
                                     AlbumSingle = rdr.GetString(2),
                                     BackupAccount = rdr.GetString(3),
                                     FBAccount = rdr.GetString(4),
                                     Url = rdr.GetString(5)
                                 });
                }
                rdr.Close();
                _connection.Close();
                return list;

            }
            catch (Exception exception)
            {
                Debug.WriteLine("GetAll: " + exception.Message);
                return null;
            }

        }


        public bool Insert(Request req)
        {
            var query =
                string.Format(
                    @"INSERT INTO Request (Artist, AlbumSingle, BackupAccount, FBAlbum,Url)
                               VALUES ('{0}','{1}','{2}','{3}','{4}')",
                    req.Artist, req.AlbumSingle, req.BackupAccount, req.FBAccount, req.Url);

            return ExecuteNonQuery(query);
        }

        public bool Update(Request req)
        {
            var query =
                string.Format(
                    @"UPDATE Request 
                            SET Artist='{0}', AlbumSingle='{1}', BackupAccount='{2}',FBAlbum='{3}',Url='{4}'
                        WHERE Id={5}",
                    req.Artist, req.AlbumSingle, req.BackupAccount, req.FBAccount, req.Url, req.Id);
            return ExecuteNonQuery(query);
        }



        public bool Delete(int reqId)
        {
            var query = string.Format("DELETE FROM Request WHERE Id={0}", reqId);
            return ExecuteNonQuery(query);
        }




        private bool ExecuteNonQuery(string query)
        {
            try
            {
                if (!connectSuccess) return false;
                var cmd = new SqlCeCommand(query, _connection);
                if (_connection.State == ConnectionState.Closed)
                    _connection.Open();

                var result = cmd.ExecuteNonQuery();
                cmd.Dispose();
                _connection.Close();
                return result > 0;
            }
            catch (Exception exception)
            {
                Debug.WriteLine("Execute non query: " + exception.Message);
                return false;
            }

        }



    }
}